package com.xxxy.jsq.dao;



import com.xxxy.jsq.dbhelper.C3P0Utils;
import com.xxxy.jsq.model.Department;
import com.xxxy.jsq.model.Page;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DepartmentDao implements BaseDao<Department> {
    //yyyyyy
    //
    private C3P0Utils db = null;
    public DepartmentDao() {
        this.db = new C3P0Utils();
    }
    @Override
    public String add(Department model) {

        Connection conn = null; // 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        sqlStr.append("insert into pa_department(department_name,department_pid,department_flag) ");
        sqlStr.append("values(?,?,?) ");
        conn = db.getConnection();
        // 调用同名检测
        boolean flag = isSame(model, true);
        if (flag == false) {
            int num = 0;
            try {
                pstm = conn.prepareStatement(sqlStr.toString());
                pstm.setString(1, model.getDepartmentName());
                pstm.setString(2, model.getDepartmentPid());
                pstm.setString(3, model.getDepartmentFlag());
                num = pstm.executeUpdate();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            if (num > 0) {
                return "Ok";
            } else {
                return "Err";
            }
        } else {
            return "Same";
        }
    }

    @Override
    public String update(Department model) {
        Connection conn = null;// 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        boolean flag=isSame(model,false);
        if(flag==true){
            return "Same";
        }
        sqlStr.append("update pa_department set ");
        if (model.getDepartmentName() != null && !model.getDepartmentName().equals("")) {
            sqlStr.append(" department_name='" + model.getDepartmentName() + "'");
        }
        if (model.getDepartmentFlag() != null && !model.getDepartmentFlag().equals("")) {
            sqlStr.append(" ,department_flag='" + model.getDepartmentFlag() + "'");
        }
        if (model.getDepartmentPid() != null && !model.getDepartmentPid().equals("")) {
            sqlStr.append(" ,department_pid='" + model.getDepartmentPid() + "'");
        }
        sqlStr.append(" where department_id=?");
        int num = 0;
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, model.getDepartmentId());
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return e.getMessage();
        }
        if (num > 0) {
            return "Ok";
        } else {
            return "Err";
        }
    }

    @Override
    public String delete(Department model) {

        return null;
    }

    @Override
    public String deleteById(Long cid) {
        Connection conn = null;// 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        sqlStr.append("delete from pa_department where department_id=? ");
        conn = db.getConnection();
        int num = 0;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, cid);
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return e.getMessage();
        }
        if (num > 0) {
            return "Ok";
        } else {
            return "Err";
        }

    }

    @Override
    public List<Department> getAllData(Department model) {
        Connection conn = null;// 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        List<Department> list = new ArrayList<Department>();
        sqlStr.append(" SELECT * from pa_department ");
        sqlStr.append("where 1=1 ");
        if (model.getDepartmentName() != null && !model.getDepartmentName().equals("")) {
            sqlStr.append("and department_name='" + model.getDepartmentName() + "' ");
        }
        if (model.getDepartmentFlag() != null && !model.getDepartmentFlag().equals("")) {
            sqlStr.append(" and department_flag='" + model.getDepartmentFlag() + "' ");
        }
        if (model.getDepartmentPid() != null && !model.getDepartmentPid().equals("")) {
            sqlStr.append(" and department_pid='" + model.getDepartmentPid() + "' ");
        }
        sqlStr.append(" ORDER BY department_stmp asc,department_id asc,department_name asc ");
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();// 执行查询操作select
            while (rs.next()) {
                Department md = new Department();
                md.setDepartmentId(rs.getLong("department_id"));
                md.setDepartmentName(rs.getString("department_name"));
                md.setDepartmentFlag(rs.getString("department_flag").equals("0") ? "启用" : "禁用");
                md.setDepartmentPid(rs.getString("department_pid"));
                list.add(md);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public Department findById(Long cid) {
        Connection conn = null;// 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        sqlStr.append(" SELECT * from pa_department  ");
        sqlStr.append("where 1=1 and department_id=? ");
        conn = db.getConnection();
        Department md = new Department();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, cid);
            rs = pstm.executeQuery();// 执行查询操作select
            while (rs.next()) {
                md.setDepartmentId(rs.getLong("department_id"));
                md.setDepartmentName(rs.getString("department_name"));
                md.setDepartmentFlag(rs.getString("department_flag").equals("0") ? "启用" : "禁用");
                md.setDepartmentPid(rs.getString("department_pid"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return md;
    }

    @Override
    public Department find(Department model) {
        Connection conn = null;// 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        sqlStr.append(" SELECT * from pa_department ");
        sqlStr.append("where 1=1 ");
        if (model.getDepartmentName() != null && !model.getDepartmentName().equals("")) {
            sqlStr.append("and department_name='" + model.getDepartmentName() + "' ");
        }
        if (model.getDepartmentFlag() != null && !model.getDepartmentFlag().equals("")) {
            sqlStr.append(" and d.department_flag='" + model.getDepartmentFlag() + "' ");
        }
        if (model.getDepartmentPid() != null && !model.getDepartmentPid().equals("")) {
            sqlStr.append(" and d.department_pid='" + model.getDepartmentPid() + "' ");
        }
        conn = db.getConnection();
        Department md = new Department();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();// 执行查询操作select
            while (rs.next()) {
                md.setDepartmentId(rs.getLong("department_id"));
                md.setDepartmentName(rs.getString("department_name"));
                md.setDepartmentFlag(rs.getString("department_flag").equals("0") ? "启用" : "禁用");
                md.setDepartmentPid(rs.getString("department_pid"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return md;
    }

    @Override
    public boolean isSame(Department model, boolean sflag) {
        Connection conn = null; // 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        sqlStr.append(" select * from pa_department where department_mame=? ");
        if (sflag == false) {
            sqlStr.append("and department_id!='" + model.getDepartmentId() + "' ");
        }
        conn = db.getConnection();
        boolean flag = false;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setString(1, model.getDepartmentName());
            rs = pstm.executeQuery();
            if (rs.next()) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return flag;
    }

    @Override
    public List<Department> getAllDataByPage(Department model, Page page) {
        Connection conn = null;// 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        List<Department> list = new ArrayList<Department>();
        sqlStr.append(" SELECT d.*,fd.department_name from pa_department d ");
        sqlStr.append("left join pa_department fd on fd.department_id=d.department_pid ");
        sqlStr.append("where 1=1 ");
        if (model.getDepartmentName() != null && !model.getDepartmentName().equals("")) {
            sqlStr.append("and d.department_name like '%" + model.getDepartmentName() + "%' ");
        }
        if (model.getDepartmentFlag() != null && !model.getDepartmentFlag().equals("")) {
            sqlStr.append(" and d.department_flag='" + model.getDepartmentFlag() + "' ");
        }
        if (model.getDepartmentPid() != null && !model.getDepartmentPid().equals("")) {
            sqlStr.append(" and d.department_pid='" + model.getDepartmentPid() + "' ");
        }
        sqlStr.append(" ORDER BY d.department_stmp asc,d.department_id asc,d.department_name asc ");
        sqlStr.append(" limit ?,? ");
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setInt(1, (page.getCurrentPage() - 1) * page.getCount());// 起始位置
            pstm.setInt(2, page.getCount());
            rs = pstm.executeQuery();// 执行查询操作select
            while (rs.next()) {
                Department md = new Department();
                md.setDepartmentId(rs.getLong("department_id"));
                md.setDepartmentName(rs.getString("department_name"));
                md.setDepartmentFlag(rs.getString("department_flag").equals("0") ? "启用" : "禁用");
                md.setDepartmentPid(rs.getString("department_pid"));
                md.setPdname(rs.getString("fd.department_name"));
                list.add(md);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int getCount(Department model) {
        Connection conn = null;// 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        sqlStr.append("select count(*) from pa_department d ");
        sqlStr.append("where 1=1 ");
        if (model.getDepartmentName() != null && !model.getDepartmentName().equals("")) {
            sqlStr.append("and department_name='" + model.getDepartmentName() + "' ");
        }
        if (model.getDepartmentFlag() != null && !model.getDepartmentFlag().equals("")) {
            sqlStr.append(" and department_flag='" + model.getDepartmentFlag() + "' ");
        }
        if (model.getDepartmentPid() != null && !model.getDepartmentPid().equals("")) {
            sqlStr.append(" and department_pid='" + model.getDepartmentPid() + "' ");
        }
        conn = db.getConnection();
        int total = 0;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            if (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return total;
    }

    @Override
    public List<Department> getAllky() {
        Connection conn = null;// 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        List<Department> list = new ArrayList<Department>();
        sqlStr.append("select * from pa_department where department_flag='0' ");//0启用，1禁用
        sqlStr.append("order by department_name asc ");
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();// 执行查询操作select
            while (rs.next()) {
                Department md = new Department();
                md.setDepartmentId(rs.getLong("department_id"));
                md.setDepartmentName(rs.getString("department_name"));
                list.add(md);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        }
        return list;
    }
}
